import sqlite3
import GV

# region BlockDb
BlockDbFileName = GV.BlockDbFileName
dbConn = sqlite3.connect(BlockDbFileName)
print("Opened BlockDb successfully")

#创建表Block
strSql = "CREATE TABLE IF NOT EXISTS [Block](\
  [B_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0, \
  [B_Filename] TEXT NOT NULL UNIQUE, \
  [B_BlockID] INT64 DEFAULT 0, \
  [B_BlockHash] TEXT, \
  [B_Timestamp] FLOAT DEFAULT 0, \
  [B_PrevBlockID] INT64 DEFAULT 0, \
  [B_PrevBlockHash] TEXT, \
  [B_Confirmed] INTEGER DEFAULT 0, \
  [B_Forked] INTEGER DEFAULT 0);"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [Block] Successfully.")

#创建表MiningDifficulty
strSql = "CREATE TABLE IF NOT EXISTS [MiningDifficulty](\
  [MD_Phase] INT PRIMARY KEY, \
  [MD_MiningDifficulty] INT) WITHOUT ROWID;"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [MiningDifficulty] Successfully.")

# 创建表AccountInfo
strSql = "CREATE TABLE IF NOT EXISTS [AccountInfo](\
  [AI_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [AI_AccountAddress] TEXT UNIQUE, \
  [AI_BlockID] INTEGER DEFAULT 0, \
  [AI_TokenCount] FLOAT DEFAULT 0);"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [AccountInfo] Successfully.")

# 创建表TransactionInfo
strSql = "CREATE TABLE IF NOT EXISTS [TransactionInfo](\
  [TI_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [TI_TranHash] TEXT , \
  [TI_B_RecordID] INTEGER REFERENCES [Block]([B_RecordID]));"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [TransactionInfo] Successfully.")



# close
dbConn.close()
#endregion

#region ServerUseDb
ServerDbFilename = GV.ServerDbFilename
dbConn = sqlite3.connect(ServerDbFilename)
print("Opened ServerUseDb successfully")

strSql = "CREATE TABLE IF NOT EXISTS [ServerUseBlockList](\
  [SUBL_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT,\
  [SUBL_Filename] TEXT, \
  [SUBL_Type] INTEGER DEFAULT 1, \
  [SUBL_Hash] TEXT, \
  [SUBL_BlockID] INTEGER, \
  [SUBL_ProviderIP] VARCHAR(200), \
  [SUBL_ProviderPort] INTEGER, \
  [SUBL_Timestampe] FLOAT, \
  UNIQUE([SUBL_Filename], [SUBL_Hash], [SUBL_ProviderIP], [SUBL_ProviderPort]));"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
dbConn.commit()
print("CREATE TABLE [ServerUseBlockList] Successfully.")

dbConn.close()
#endregion

#region ClientUseDb
ClientDbFilename = GV.ClientDbFilename
dbConn = sqlite3.connect(ClientDbFilename)
print("Opened ClientUseDb successfully")
strSql = "CREATE TABLE IF NOT EXISTS [ClientUseBlockListFromServer](\
  [CUBLFS_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [CUBLFS_Filename] TEXT, \
  [CUBLFS_Type] INTEGER DEFAULT 1, \
  [CUBLFS_Hash] TEXT, \
  [CUBLFS_BlockID] INTEGER, \
  [CUBLFS_ProviderIP] VARCHAR(200), \
  [CUBLFS_ProviderPort] INTEGER, \
  [CUBLFS_Timestampe] FLOAT, \
  UNIQUE([CUBLFS_Filename], [CUBLFS_Hash], [CUBLFS_ProviderIP], [CUBLFS_ProviderPort]));"

c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [ClientUseBlockListFromServer] Successfully.")
dbConn.close()
#endregion


# region 快速检索仿真
SimDbFilename = GV.SimDbFilename
dbConn = sqlite3.connect(SimDbFilename)
print("Opened BlockDb successfully")

# 创建表SimAccountList
strSql = "CREATE TABLE IF NOT EXISTS [SimAccountList](\
  [SAL_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [SAL_AccountName] TEXT ,\
  [SAL_AccountAddress] TEXT UNIQUE);"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [SimAccountList] Successfully.")

# 创建表SimBlocks
strSql = "CREATE TABLE IF NOT EXISTS \
[SimBlocks](\
[SB_blockId] INT PRIMARY KEY,\
[SB_previousBlockHash] TEXT,\
[SB_blockTranStr] TEXT,\
[SB_minerAddress] TEXT,\
[SB_miningRandomNum] TEXT,\
[SB_miningMsg] TEXT,\
[SB_miningDifficulty] TEXT,\
[SB_timestamp] TEXT,\
[SB_part1Str] TEXT,\
[SB_thisBlockHash] TEXT UNIQUE) WITHOUT ROWID; "
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [SimBlocks] Successfully.")

# 创建表SimTransactions
strSql = "CREATE TABLE IF NOT EXISTS \
[SimTransactions](\
[ST_RowId] INTEGER PRIMARY KEY,\
[ST_BlockId] INTEGER,\
[ST_sender] TEXT,\
[ST_recipient] TEXT,\
[ST_value] TEXT,\
[ST_fee] TEXT,\
[ST_timestamp] TEXT,\
[ST_scriptstr] TEXT,\
[ST_specinfo] TEXT,\
[ST_part1Str] TEXT,\
[ST_signStr] TEXT,\
[ST_pubkeyStr] TEXT,\
[ST_part2Str] TEXT,\
[ST_tranHash] TEXT UNIQUE) WITHOUT ROWID; "
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [SimTransactions] Successfully.")

# 创建表DistinctAddress
strSql = "CREATE TABLE IF NOT EXISTS \
[DistinctAddress](\
[DA_RowId] INTEGER PRIMARY KEY AUTOINCREMENT,\
[DA_recipient] UNIQUE); "
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [DistinctAddress] Successfully.")

# close
dbConn.close()
#endregion

